/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80032 (8.0.32)
 Source Host           : localhost:3306
 Source Schema         : dorm

 Target Server Type    : MySQL
 Target Server Version : 80032 (8.0.32)
 File Encoding         : 65001

 Date: 27/06/2023 09:27:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `cid` bigint NOT NULL AUTO_INCREMENT,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'e10adc3949ba59abbe56e057f20f883e' COMMENT '学生密码',
  `sid` bigint NOT NULL COMMENT '学生id',
  `update_time` datetime NOT NULL COMMENT '密码更新时间',
  PRIMARY KEY (`cid`) USING BTREE,
  UNIQUE KEY `学生id` (`sid`) USING BTREE,
  CONSTRAINT `学生id` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of account
-- ----------------------------
BEGIN;
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (1, '7fa94b65ccca7acb42888c5a43e59213', 1, '2023-06-16 09:47:41');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (2, 'e10adc3949ba59abbe56e057f20f883e', 2, '2023-06-10 22:42:42');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (3, 'e10adc3949ba59abbe56e057f20f883e', 3, '2023-06-11 16:14:16');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (4, 'e10adc3949ba59abbe56e057f20f883e', 4, '2023-06-11 16:14:16');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (5, 'e10adc3949ba59abbe56e057f20f883e', 5, '2023-06-22 23:40:53');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (7, 'e10adc3949ba59abbe56e057f20f883e', 7, '2023-06-16 10:31:17');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (8, 'e10adc3949ba59abbe56e057f20f883e', 8, '2023-06-14 09:42:00');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (11, '7e204824783a648c4280466887d0d35f', 11, '2023-06-16 15:41:33');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (15, 'e10adc3949ba59abbe56e057f20f883e', 14, '2023-06-22 23:41:24');
INSERT INTO `account` (`cid`, `password`, `sid`, `update_time`) VALUES (16, 'e10adc3949ba59abbe56e057f20f883e', 15, '2023-06-22 23:45:10');
COMMIT;

-- ----------------------------
-- Table structure for board
-- ----------------------------
DROP TABLE IF EXISTS `board`;
CREATE TABLE `board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of board
-- ----------------------------
BEGIN;
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (1, '凡有以下情况者，不得住宿\n有不良嗜好者，如酗酒、赌博的。', '2023-06-22 21:34:31');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (2, '室内禁止私自接配电线及装接电器。', '2023-06-17 18:44:07');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (3, '室内不得使用或存放危险及违禁物品。', '2023-06-17 18:44:15');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (4, '个人棉被、垫被起床后须叠齐。', '2023-06-17 18:44:24');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (5, '就寝后不得有影响他人睡眠行为。', '2023-06-17 18:44:45');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (6, '垃圾等应集中于指定场所倾倒。', '2023-06-17 18:46:42');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (7, '节俭用水，用电，按时熄灯', '2023-06-17 18:58:13');
INSERT INTO `board` (`id`, `message`, `update_time`) VALUES (8, '不准私自外宿', '2023-06-17 19:08:29');
COMMIT;

-- ----------------------------
-- Table structure for checkdorm
-- ----------------------------
DROP TABLE IF EXISTS `checkdorm`;
CREATE TABLE `checkdorm` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `hid` bigint DEFAULT NULL COMMENT '来查寝的学生',
  `sid` bigint DEFAULT NULL COMMENT '被查寝的学生',
  `check_time` datetime DEFAULT NULL COMMENT '查寝时间',
  `check_state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '查寝状态（未归，已到，已请假）',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `hid` (`hid`) USING BTREE,
  KEY `checksid` (`sid`) USING BTREE,
  CONSTRAINT `checksid` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `hid` FOREIGN KEY (`hid`) REFERENCES `helper` (`hid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of checkdorm
-- ----------------------------
BEGIN;
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (1, 1, 2, '2023-06-14 16:13:57', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (2, 1, 3, '2023-06-14 16:14:39', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (3, 1, 4, '2023-06-14 16:15:01', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (4, 2, 7, '2023-06-18 15:57:23', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (6, 2, 4, '2023-06-19 16:28:04', '未归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (13, 3, 1, '2023-06-20 16:19:32', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (14, 3, 3, '2023-06-20 16:19:37', '未归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (15, 3, 4, '2023-06-20 16:19:39', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (16, 3, 7, '2023-06-20 16:19:41', '已归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (17, 3, 8, '2023-06-20 16:19:43', '未归');
INSERT INTO `checkdorm` (`id`, `hid`, `sid`, `check_time`, `check_state`) VALUES (18, 3, 11, '2023-06-20 16:19:45', '已归');
COMMIT;

-- ----------------------------
-- Table structure for dormitory
-- ----------------------------
DROP TABLE IF EXISTS `dormitory`;
CREATE TABLE `dormitory` (
  `did` bigint NOT NULL AUTO_INCREMENT COMMENT '宿舍id',
  `dormitory` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '宿舍号',
  `max_capacity` int DEFAULT '6' COMMENT '最大人数',
  `now_capacity` int DEFAULT '0' COMMENT '目前人数',
  `sex` int DEFAULT NULL COMMENT '1表示男生宿舍，2表示女生宿舍',
  PRIMARY KEY (`did`) USING BTREE,
  UNIQUE KEY `宿舍号唯一` (`dormitory`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of dormitory
-- ----------------------------
BEGIN;
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (1, '236', 6, 6, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (2, '237', 6, 2, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (3, '238', 6, 2, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (4, '239', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (5, '240', 6, 0, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (6, '241', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (7, '242', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (8, '243', 6, 0, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (9, '244', 6, 0, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (10, '245', 6, 0, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (11, '246', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (12, '247', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (13, '248', 6, 0, 1);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (14, '249', 6, 0, 2);
INSERT INTO `dormitory` (`did`, `dormitory`, `max_capacity`, `now_capacity`, `sex`) VALUES (15, '250', 6, 0, 1);
COMMIT;

-- ----------------------------
-- Table structure for helper
-- ----------------------------
DROP TABLE IF EXISTS `helper`;
CREATE TABLE `helper` (
  `hid` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `sid` bigint DEFAULT NULL COMMENT '学生编号',
  `state` int DEFAULT NULL COMMENT '1为任命，0为暂时不任用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`hid`) USING BTREE,
  KEY `sid` (`sid`) USING BTREE,
  CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of helper
-- ----------------------------
BEGIN;
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (1, 3, 1, '2023-06-14 16:42:25', '2023-06-22 21:33:16');
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (2, 4, 1, '2023-06-15 15:12:49', '2023-06-22 21:33:25');
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (3, 1, 1, '2023-06-15 15:13:08', '2023-06-15 15:13:10');
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (4, 2, 1, '2023-06-16 10:07:22', '2023-06-16 10:07:22');
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (5, 6, 1, '2023-06-16 10:10:37', '2023-06-16 11:06:02');
INSERT INTO `helper` (`hid`, `sid`, `state`, `create_time`, `update_time`) VALUES (6, 7, 1, '2023-06-16 10:14:44', '2023-06-16 10:14:44');
COMMIT;

-- ----------------------------
-- Table structure for leaves
-- ----------------------------
DROP TABLE IF EXISTS `leaves`;
CREATE TABLE `leaves` (
  `leaveid` bigint NOT NULL AUTO_INCREMENT COMMENT '请假编号',
  `sid` bigint NOT NULL COMMENT '请假学生',
  `begin_time` datetime NOT NULL COMMENT '请假开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '请假借口（理由）',
  PRIMARY KEY (`leaveid`) USING BTREE,
  KEY `leavesid` (`sid`) USING BTREE,
  CONSTRAINT `leavesid` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of leaves
-- ----------------------------
BEGIN;
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (1, 1, '2023-06-13 09:56:44', '2023-06-15 09:56:48', '被狗咬了去打疫苗');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (2, 4, '2023-06-14 15:12:10', '2023-06-15 15:12:16', '看坤坤演唱会');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (3, 2, '2023-06-16 08:59:38', '2023-06-22 08:59:44', '不想读了回家休息几天');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (4, 5, '2023-06-19 09:18:40', '2023-07-01 09:18:45', '被卷抑郁了去旅游两周');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (5, 1, '2023-06-15 09:20:05', '2023-06-17 09:20:12', '我家猫和狗打架回家劝架');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (6, 3, '2023-06-22 09:24:05', '2023-06-24 09:24:10', '回家过节');
INSERT INTO `leaves` (`leaveid`, `sid`, `begin_time`, `end_time`, `reason`) VALUES (7, 3, '2023-06-25 08:00:00', '2023-06-26 08:00:00', '这破书不读也罢');
COMMIT;

-- ----------------------------
-- Table structure for sd
-- ----------------------------
DROP TABLE IF EXISTS `sd`;
CREATE TABLE `sd` (
  `sd_id` bigint NOT NULL AUTO_INCREMENT,
  `sid` bigint DEFAULT NULL,
  `did` bigint DEFAULT NULL,
  PRIMARY KEY (`sd_id`) USING BTREE,
  UNIQUE KEY `1类` (`sid`) USING BTREE,
  KEY `2类` (`did`) USING BTREE,
  CONSTRAINT `1类` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `2类` FOREIGN KEY (`did`) REFERENCES `dormitory` (`did`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of sd
-- ----------------------------
BEGIN;
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (55, 1, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (56, 2, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (57, 3, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (58, 5, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (59, 7, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (60, 8, 1);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (61, 4, 3);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (62, 11, 3);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (64, 14, 2);
INSERT INTO `sd` (`sd_id`, `sid`, `did`) VALUES (65, 15, 2);
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '性别',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号',
  `profession` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '专业',
  `sno` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `手机不能重复` (`phone`) USING BTREE,
  UNIQUE KEY `学号不能重复` (`sno`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (1, '徐晨标', '男', '18876349934', '软件工程', '123456');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (2, '吴勇平', '男', '1343244', '软件工程', '654321');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (3, '小徐', '男', '18826372811', '生物专业', '6258762811');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (4, '小李', '女', '13482766771', '土木工程', '1434256771');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (5, '老王', '男', '13456788775', '会计ll', '8947708775');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (7, '小花', '男', '18654340985', '计算机科学与技术', '1544990985');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (8, '大洼', '男', '18654340986', '计算机', '4289390986');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (11, '老李', '女', '18876345523', '采购员', '1024385523');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (14, '王三', '男', '17765434435', '保安', '7095474435');
INSERT INTO `student` (`id`, `sname`, `sex`, `phone`, `profession`, `sno`) VALUES (15, '大卫', '男', '15567876543', '写书', '4744606543');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
